package Dao;

import UnderAccessDBclass.Druid_utils;
import bean.SC;

import java.util.ArrayList;

public class SCDao {
    public ArrayList<SC> selectAll() {
        ArrayList<SC> scList = new ArrayList<>();
        String sql = "SELECT s.sno,sname,c.cno,cname,grade FROM sc,student s,course c " +
                "WHERE sc.cno=c.cno AND sc.sno = s.sno";
        ArrayList<Object[]> list = Druid_utils.executeQuery(sql, null);
        for (Object[] objects : list) {
            SC sc = new SC();
            sc.setSno((String) objects[0]);
            sc.setSname((String) objects[1]);
            sc.setCno((String) objects[2]);
            sc.setCname((String) objects[3]);
            sc.setGrade(objects[4] == null ? "未修完": objects[4].toString());
            scList.add(sc);
        }
        return scList;
    }

    public ArrayList<SC> select(String sno, String cno) {
        String sql = "SELECT s.sno,sname,c.cno,cname,grade FROM sc,student s,course c " +
                "WHERE sc.cno=c.cno AND sc.sno = s.sno AND s.sno = ? AND c.cno = ?";
        ArrayList<Object[]> list = Druid_utils.executeQuery(sql, new Object[]{sno, cno});
        ArrayList<SC> scList = new ArrayList<>();
        for (Object[] objects : list) {
            SC sc = new SC();
            sc.setSno((String) objects[0]);
            sc.setSname((String) objects[1]);
            sc.setCno((String) objects[2]);
            sc.setCname((String) objects[3]);
            sc.setGrade(objects[4] == null ? "未修完": objects[4].toString());
            scList.add(sc);
        }
        return scList;
    }

    public ArrayList<SC> selectBySno(String sno) {
        ArrayList<SC> scList = new ArrayList<>();
        String sql = "SELECT s.sno,sname,c.cno,cname,grade FROM sc,student s,course c " +
                "WHERE sc.cno=c.cno AND sc.sno = s.sno AND s.sno = ?";
        ArrayList<Object[]> list = Druid_utils.executeQuery(sql, new Object[]{sno});
        for (Object[] objects : list) {
            SC sc = new SC();
            sc.setSno((String) objects[0]);
            sc.setSname((String) objects[1]);
            sc.setCno((String) objects[2]);
            sc.setCname((String) objects[3]);
            sc.setGrade(objects[4] == null ? "未修完": objects[4].toString());
            scList.add(sc);
        }
        return scList;
    }
    public ArrayList<SC> selectByCno(String cno) {
        ArrayList<SC> scList = new ArrayList<>();
        String sql = "SELECT s.sno,sname,c.cno,cname,grade FROM sc,student s,course c " +
                "WHERE sc.cno=c.cno AND sc.sno = s.sno AND c.cno = ?";
        ArrayList<Object[]> list = Druid_utils.executeQuery(sql, new Object[]{cno});
        for (Object[] objects : list) {
            SC sc = new SC();
            sc.setSno((String) objects[0]);
            sc.setSname((String) objects[1]);
            sc.setCno((String) objects[2]);
            sc.setCname((String) objects[3]);
            sc.setGrade(objects[4] == null ? "未修完": objects[4].toString());
            scList.add(sc);
        }
        return scList;
    }

    public ArrayList<SC> insert(String sno, String cno) {
        String sql = "INSERT INTO sc VALUES(?,?,NULL)";
        Object[] params = {sno, cno};
        Druid_utils.executeUpdate(sql, params);
        return selectAll();
    }
    public ArrayList<SC> delete(String sno, String cno) {
        String sql = "DELETE FROM sc WHERE sno=? AND cno=?";
        Object[] params = {sno, cno};
        Druid_utils.executeUpdate(sql, params);
        return selectAll();
    }

    public ArrayList<SC> update(String sno, String cno, String grade) {
        String sql = "UPDATE sc SET grade=? WHERE sno=? AND cno=?";
        Object[] params = {grade, sno, cno};
        Druid_utils.executeUpdate(sql, params);
        return selectAll();
    }

    public static void main(String[] args) {
        SCDao scDao = new SCDao();
        ArrayList<SC> scList = scDao.selectAll();
        for (SC sc : scList) {
            System.out.println(sc);
        }
        System.out.println();
//        scList = scDao.delete("201939", "20740061");
//        for (SC sc : scList) {
//            System.out.println(sc);
//        }
//        System.out.println();
//        scList=scDao.insert("201939", "20740061");
//        for (SC sc : scList) {
//            System.out.println(sc);
//        }
        SC sc1 = new SC();
        sc1.setSno("200307");
        sc1.setCno("20740061");

        System.out.println(scList.indexOf(sc1));
        System.out.println(scList.contains(sc1));

        scList = scDao.select("200310", "20740061");
        for (SC sc : scList) {
            System.out.println(sc);
        }
    }
}
